package edu.nctu.csie.jichang.database.create;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.model.Converter;
import edu.nctu.csie.jichang.database.model.cell.ColumnInfo;
import edu.nctu.csie.jichang.database.model.cell.ColumnInfoValue;
import edu.nctu.csie.jichang.database.model.cell.RefTableMapping;
import edu.nctu.csie.jichang.database.model.cell.SchemaTable;
import edu.nctu.csie.jichang.database.model.connection.AbstractDBConnection;
import edu.nctu.csie.jichang.database.model.connection.IDBConnection;
import edu.nctu.csie.jichang.database.util.StringUtil;

/**
 * @author Jichang
 */
public class CreateData{

	private static final Logger LOG = LoggerFactory.getLogger(CreateData.class);
	
	private IDBConnection conn = null;
	private SchemaTable SchemaTable = null;
	private List<ColumnInfoValue> condition = null;
	private LoginInfo userInfo = null;
	
	public CreateData(SchemaTable pSchemaTable, List<ColumnInfoValue> pCondition, LoginInfo pUserInfo){
		SchemaTable = pSchemaTable;
		condition = pCondition;
		userInfo = pUserInfo;
	}

	public void start()throws Exception{
		conn = AbstractDBConnection.getInstance(userInfo);
		try{
			conn.setAutoCommit(false);
			doCreate(SchemaTable,condition,"");
			conn.commit();
		}catch (Exception e) {
			conn.rollback();
			LOG.error("",e);
		}finally{
			conn.close();
		}
	}
	
	private List<ColumnInfoValue> doCreate(SchemaTable pTable,List<ColumnInfoValue> pCondition,String pOpenField)throws Exception{
		if(!conn.isHasRow(pTable.getTable(),pCondition)){
			List<SchemaTable> tAllRefTable = conn.getRefTable(pTable);
			List<ColumnInfoValue> tCondition1 = new ArrayList<ColumnInfoValue>();
			List<ColumnInfoValue> tCondition2 = new ArrayList<ColumnInfoValue>();
			String tOpenField1 = null ;
			for(SchemaTable tRefTable:tAllRefTable){
				tCondition1 = modifyCondition(pTable,tRefTable,pCondition,null,true);
				tOpenField1 = modifyOpenfield(pTable,tRefTable);
				tCondition2 = doCreate(tRefTable,tCondition1,tOpenField1);
				pCondition  = modifyCondition(pTable,tRefTable,pCondition,tCondition2,false);
			}
			if(insert(pTable,pCondition)){
				return selectField(pTable,pCondition,pOpenField);
			}else{
				return pCondition;
			}
		}
		else{
			return selectField(pTable,pCondition,pOpenField); 
		}
	}

	private List<ColumnInfoValue> modifyCondition(SchemaTable pTable,SchemaTable pRFtable,List<ColumnInfoValue> pCondition,List<ColumnInfoValue> pCondition2,boolean pType)throws Exception{
		if(pType){
			List<ColumnInfoValue> tCondList = new ArrayList<ColumnInfoValue>();
			for(RefTableMapping t :conn.getRefTableMapping(pTable, pRFtable)){
				for(ColumnInfoValue tCDT : pCondition){
					if( t.getColumnName().equals(tCDT.getColumnInfo().getName())){
						tCondList.add(new ColumnInfoValue(t.getRefColumnName(),tCDT.getColumnInfo().getColumnType(),tCDT.getValue()));
					}
				}
			}
			return tCondList;
		}
		else{
			boolean tNotEqual = true;
			for(ColumnInfoValue tCDT2 : pCondition2){
				tNotEqual=true;
				for(ColumnInfoValue tCDT1 : pCondition){
					if( (tCDT1.getColumnInfo().getName()).equals(tCDT2.getColumnInfo().getName()) )
						tNotEqual = false;
				}
				if(tNotEqual)
					pCondition.add(tCDT2);
			}
			return pCondition;
		}
	}
	
	private String modifyOpenfield(SchemaTable pTable,SchemaTable pRFtable)throws Exception{
		StringBuffer tOutput = new StringBuffer();
		for(RefTableMapping t :conn.getRefTableMapping(pTable, pRFtable)){
			tOutput.append(",").append(t.getRefColumnName()).append(" ").append(t.getColumnName());
		}
		return tOutput.substring(1);
	}	

	private List<ColumnInfoValue> selectField(SchemaTable pTable, List<ColumnInfoValue> pCondition,String pOpenField)throws Exception{
		List<ColumnInfoValue> tCondList = new ArrayList<ColumnInfoValue>(); 
		if(StringUtil.isEmptyOrSpace(pOpenField)){
			return tCondList;
		}
		StringBuffer tWhere = new StringBuffer();
		if(!pCondition.isEmpty()){
			tWhere.append(" where 1=1 ");
			for(ColumnInfoValue tCDT : pCondition){
				tWhere.append(" and ").append( tCDT.getColumnInfo().getName() );
				tWhere.append( StringUtil.isEmptyOrSpace( tCDT.toSQL()) ? " is " : " = " );
				tWhere.append(  tCDT.toSQL() );
			}
		}
		StringBuffer tSQL = new StringBuffer();
		tSQL.append("select ").append(pOpenField).append(" from ").append( pTable.getTable() ).append(tWhere);
		PreparedStatement tPstmt = conn.getConnection().prepareStatement(tSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY);
		ResultSet tRS = tPstmt.executeQuery();
		tRS.last();
		int tCount = tRS.getRow();
		if(tCount<1){
			tRS.close();
			tPstmt.close();
			return tCondList;
		} 
		tRS.absolute((int)(Math.random()*tRS.getRow())+1);
		for(int i=0;i<tRS.getMetaData().getColumnCount();i++){			
			tCondList.add(new ColumnInfoValue(tRS.getMetaData().getColumnName(i+1),Converter.getColumnType(tRS.getMetaData().getColumnType(i+1)),tRS.getObject(i+1)));
		}
		tRS.close();
		tPstmt.close();
		return tCondList;
	}

	private boolean insert(SchemaTable pTable,List<ColumnInfoValue> pCondition)throws Exception{
		List<ColumnInfo>  tColumnInfos = conn.getColumnInfo(pTable.getTable());
		StringBuffer insert = new StringBuffer();
		insert.append("Insert Into ").append( pTable.getTable()).append("(");
		for(ColumnInfo c:  tColumnInfos){
			if("_id".equals(c.getName())){
				for(ColumnInfoValue tCDT : pCondition){
					if( c.getName().equals(tCDT.getColumnInfo().getName())){
						insert.append(c.getName()).append(",");
					}
				}
			}else{
				insert.append(c.getName()).append(",");
			}
		}
		insert = new StringBuffer(StringUtil.trimLastChar(insert.toString()));
		insert.append(") Values (");
		for(ColumnInfo c: tColumnInfos ){
			if("_id".equals(c.getName())){
				for(ColumnInfoValue tCDT : pCondition){
					if( c.getName().equals(tCDT.getColumnInfo().getName())){
						insert.append(tCDT.toSQL()).append(",");
					}
				}
			}else{
				insert.append(getRightData(c,pCondition)).append(",");
			}
		}
		insert = new StringBuffer(StringUtil.trimLastChar(insert.toString()));
		insert.append(");");
		System.err.println(insert.toString());
		conn.executeUpdate(insert.toString());
		return true;
	}
	
	private Object getRightData(ColumnInfo pColumnInfo,List<ColumnInfoValue> pCondition)throws Exception{
		for(ColumnInfoValue tCDT : pCondition){
			if( pColumnInfo.getName().equals(tCDT.getColumnInfo().getName())){
				return tCDT.toSQL();
			}
		}
		if(pColumnInfo.isNullable()){
			return "null";
		}
		switch (pColumnInfo.getColumnType()) {
			case Boolean:
				return "'1'";
			case Date:
				return "2011-11-11";
			case Integer:
				return 1;
			case String:
				return "'string'";
			case Binary:
				throw new Exception("Can't support binary type");
		}
		return "null";
	}

}